﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using Telerik.WinControls;
using Telerik.WinControls.UI;
using System.IO;
using Microsoft.SolverFoundation.Common;
using Microsoft.SolverFoundation.Solvers;
using Microsoft.SolverFoundation.Services;
using System.Data.OleDb;



namespace PlanningDepartment
{
    class Process
    {
        public SqlConnection Connect()//Ket Noi
        {

            /*string Ketnoi = @"
            server=.\sqlexpress;
            integrated security=true;
            database=Planning";
            //*/
           string Ketnoi = @"Data Source=192.168.1.5;Initial Catalog=Planning;User ID=sa;Password=vevoianh;";
            SqlConnection conn = new SqlConnection(Ketnoi);
            return conn;
        }
        public SqlConnection Connect_DBScansia()
        {
            /*string Ketnoi = @"
            server=.\sqlexpress;
            integrated security=true;
            database=DataBase_ScansiaPacific2013";
            //*/
            string Ketnoi = @"Data Source=192.168.1.5;Initial Catalog=DataBase_ScansiaPacific2013;User ID=sa;Password=vevoianh;";
            SqlConnection conn = new SqlConnection(Ketnoi);
            return conn;
        }
        public SqlConnection Connect_HRP()
        {
            /*string Ketnoi = @"
            server=.\sqlexpress;
            integrated security=true;
            database=HRP_ScansiaPacific";
            //*/
           string Ketnoi = @"Data Source=192.168.1.5;Initial Catalog=HRP_ScansiaPacific;User ID=sa;Password=vevoianh;";
            SqlConnection conn = new SqlConnection(Ketnoi);
            return conn;
        }
        public void reset_txt(RadGroupBox gr)//Xoa ky tu trong txtbox
        {
            foreach (Control ct in gr.Controls)
                if (ct is RadTextBox)
                {
                    ((RadTextBox)ct).Text = "";
                }
        }
        public void Change_data(SqlCommand cmd, SqlConnection Conn)//them,xoa,sua,delete du lieu
        {
            try
            {
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (Exception ex)//bắt lỗi insert
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
            }

        }
        public DataSet dts(string sql, SqlConnection Conn, DataSet ds1)//Khoi tao dataset
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, Conn);

            da.Fill(ds1);
            //Conn.Close();
            da.Dispose();
            return ds1;
        }
        public DataSet dts_sql(SqlCommand cmd, SqlConnection Conn, DataSet ds)
        {
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            //Conn.Close();
            return ds;
        }
       
        /*public void formatcolordatagrid(RadGridView grid)
        {
            /*int k = 0;
            for (int i = 0; i < grid.Rows.Count - 1; i++)
            {
                k = i + 1;
               // grid.Columns[0].HeaderText
                //grid.MasterGridViewTemplate.Columns[0].HeaderText = "aaaa";
                grid.Rows[i].HeaderCell.Value = k.ToString();
                if (k % 2 == 0)
                {
                    grid.Rows[i].DefaultCellStyle.BackColor = Color.AliceBlue;
                }
                else
                {
                    grid.Rows[i].DefaultCellStyle.BackColor = Color.WhiteSmoke;
                }
            }
        }*/
        public void ExportExcel(DataTable dt, string path, int rowStart, int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
        public void ExportExcel_grid(RadGridView dt, string path, int rowStart, int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dt.Rows[r].Cells[c].Value;
                }
            }

            //Thiết lập vùng điền dữ liệu

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            

            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
        public void ExportExcel_gridview_Win(DataGridView dt, string path, int rowStart, int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            int r = 0;
           
            for (r = 0; r < dt.Rows.Count; r++)
            {
                //DataRow dr = dt.Rows[r];
                
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    if (r == 0)
                    {

                        //DataRow dr = dt.Rows[r];
                        arr[r, c] = dt.Columns[c].HeaderText.ToString();
                       
                    }
                    else
                    {
                        
                        arr[r, c] = dt.Rows[r-1].Cells[c].Value;
                        
                    }
                }
               
            }

            //Thiết lập vùng điền dữ liệu

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;


            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
        public void ExportExcel_gridview_Telerik(RadGridView dt, string path, int rowStart, int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            int r = 0;

            for (r = 0; r < dt.Rows.Count; r++)
            {
                //DataRow dr = dt.Rows[r];

                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    if (r == 0)
                    {

                        //DataRow dr = dt.Rows[r];
                        arr[r, c] = dt.Columns[c].HeaderText.ToString();

                    }
                    else
                    {

                        arr[r, c] = dt.Rows[r].Cells[c].Value;

                    }
                }

            }

            //Thiết lập vùng điền dữ liệu

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;


            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
        public void cb1(RadDropDownList cb, SqlCommand cmd, SqlConnection Conn, int column1, int column2)
        {
            try
            {

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {

                    cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());
                }

                rd.Close();
            }
            catch (Exception ex)
            { MessageBox.Show("Lỗi load Combobox " + ex.Message); }

        }
        public void checktext_number(RadTextBox txt)
        {
            try
            {
                if (txt.Text.Length > 0)
                {
                    txt.Text = string.Format("{0:###,###,###,###.####}", double.Parse(txt.Text));//ép kiểu float,nếu xảy ra ngoại lệ thì bẫy lỗi ở hàm checktext_number
                    txt.SelectionStart = txt.Text.Length;
                }
            }
            catch (Exception ex)//Bẫy lỗi khi nhập chữ vào text box
            {
                string s = "Input string";
                if (ex.Message.ToString().Contains(s))
                {
                    MessageBox.Show("Giá trị nhập phải là số");
                    txt.Text = "";
                }
                else
                    MessageBox.Show(ex.Message);
            }
        }
        public int index_string(string Parent, string child)
        {
            int j = 0, i, check = -1;
            for (i = 0; i < Parent.Length; i++)
            {
                if (child[j] == Parent[i])
                {
                    if (j == child.Length - 1)
                    {
                        check = i;
                        break;
                    }
                    else
                        j++;
                }
                else
                    j = 0;
            }
            return check;
        }//Tim chuoi con trong chuoi cha va tra ve vi tri cuoi cung cua chuoi con trong chuoi cha
        public string substring_left(string parent, string child)
        {
            int index = index_string(parent, child);
            string s = "";
            if (index == child.Length - 1)
                s = "";
            if (index == parent.Length - 1)
                s = parent.Substring(0, parent.Length - child.Length);
            if (index > child.Length - 1 && index < parent.Length - 1)
                s = parent.Substring(0, index - child.Length + 1);
            return s;
        }
        public string substring_right(string parent, string child)
        {
            int index = index_string(parent, child);
            string s = "";
            if (index == child.Length - 1)
                s = parent.Substring(index + 1, parent.Length - child.Length);
            if (index == parent.Length - 1)
                s = "";
            if (index > child.Length - 1 && index < parent.Length - 1)
                s = parent.Substring(index + 1, parent.Length - index - 1);
            return s;
        }
        public void reset_group_txt(RadGroupBox gr)//Xoa ky tu trong txtbox
        {
            foreach (Control ct in gr.Controls)
                if (ct is RadTextBox)
                {
                    ((RadTextBox)ct).Text = "";
                }
        }
        public void reset_page_txt(RadPageViewPage Page)
        {
            foreach (Control ct in Page.Controls)
            {
                if (ct is RadTextBox)
                {
                    ((RadTextBox)ct).Text = "";
                }
                if (ct is RadDropDownList)
                {
                    ((RadDropDownList)ct).Text = "";
                }
            }
        }
        public void get_image(PictureBox pt, byte[] imageData)
        {
            Image newImage;
            //Read image data into a memory stream
            using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
            {
                ms.Write(imageData, 0, imageData.Length);

                //Set image variable value using memory stream.
                newImage = Image.FromStream(ms, true);
            }

            //set picture
            pt.Image = newImage;

        }

        public void Open_Word2007(string path)
        {
            System.Diagnostics.Process myprocess = System.Diagnostics.Process.Start(path);
        }
        public double[] ToiUuHoa_int(DataTable dt)
        {
            
            SimplexSolver solver = new SimplexSolver();
            int n = dt.Columns.Count-2,int_temp=0;
            int[] a = new int[n];
            for (int i = 0; i < n; i++)
            {
                solver.AddVariable("X"+i.ToString(), out a[i]);
                solver.SetBounds(a[i], 0, Rational.PositiveInfinity);
                solver.SetIntegrality(a[i], true);//Lay NGHIEM NGUYEN
            }//Khi bao bien va rang buoc cua bien
            int rangbuoc = dt.Rows.Count;
            int[] b = new int[rangbuoc]; //Tạo ràng buộc
            
            for (int i = 0; i < b.Length; i++)
            {
                
                solver.AddRow(i.ToString(), out b[i]);
                if (i == 0)
                {
                   
                    for (int k = 0; k < n; k++)
                    {
                        //MessageBox.Show(dt.Rows[i][k].ToString());
                        //int_temp = int.Parse(dt.Rows[i][k].ToString());
                        //MessageBox.Show(int_temp.ToString());
                        solver.SetCoefficient(b[0], a[k], double.Parse(dt.Rows[i][k].ToString()));
                       // MessageBox.Show(dt.Rows[i][k].ToString());
                    }
                    solver.AddGoal(b[0], 1, true);
                    //essageBox.Show("xong ham muc tieu");
                }
                if (i > 0)
                {
                    
                    for (int j = 0; j < n; j++)
                    {
                        solver.SetCoefficient(b[i], a[j], double.Parse(dt.Rows[i][j].ToString()));
                    }
                    solver.SetBounds(b[i], double.Parse(dt.Rows[i][dt.Columns.Count - 1].ToString()), Rational.PositiveInfinity);
                    //MessageBox.Show("them rang buoc "+i.ToString());
                }
               
            }
            SimplexSolverParams param = new SimplexSolverParams();
            param.MixedIntegerGenerateCuts = true;
            solver.Solve(param);
            double[] Solution=new double[a.Length];
            for (int i = 0; i < a.Length; i++)
            {
                Solution[i] = solver.GetValue(a[i]).ToDouble();
            }
            return Solution;
            //MessageBox.Show("xong roi");
            /*for (int l = 0; l < a.Length; l++)
            {
                MessageBox.Show(solver.GetValue(a[l]).ToDouble().ToString());
            }*/
        }
        public double[] ToiUuHoa_Double(DataTable dt)
        {

            SimplexSolver solver = new SimplexSolver();
            int n = dt.Columns.Count - 2;
            int[] a = new int[n];
            for (int i = 0; i < n; i++)
            {
                solver.AddVariable("X" + i.ToString(), out a[i]);
                solver.SetBounds(a[i], 0, Rational.PositiveInfinity);
               // solver.SetIntegrality(a[i], true);//RANG BUOC LAY NGHIEM NGUYEN
            }//Khi bao bien va rang buoc cua bien
            int rangbuoc = dt.Rows.Count;
            int[] b = new int[rangbuoc]; //Tạo ràng buộc

            for (int i = 0; i < b.Length; i++)
            {

                solver.AddRow(i.ToString(), out b[i]);
                if (i == 0)
                {

                    for (int k = 0; k < n; k++)
                    {
                        //MessageBox.Show(dt.Rows[i][k].ToString());
                        //int_temp = int.Parse(dt.Rows[i][k].ToString());
                        //MessageBox.Show(int_temp.ToString());
                        solver.SetCoefficient(b[0], a[k], double.Parse(dt.Rows[i][k].ToString()));
                        // MessageBox.Show(dt.Rows[i][k].ToString());
                    }
                    solver.AddGoal(b[0], 1, true);
                    //essageBox.Show("xong ham muc tieu");
                }
                if (i > 0)
                {

                    for (int j = 0; j < n; j++)
                    {
                        solver.SetCoefficient(b[i], a[j], double.Parse(dt.Rows[i][j].ToString()));
                    }
                    solver.SetBounds(b[i], double.Parse(dt.Rows[i][dt.Columns.Count - 1].ToString()), Rational.PositiveInfinity);
                    //MessageBox.Show("them rang buoc "+i.ToString());
                }

            }
            SimplexSolverParams param = new SimplexSolverParams();
            param.MixedIntegerGenerateCuts = true;
            solver.Solve(param);
            double[] Solution = new double[a.Length];
            for (int i = 0; i < a.Length; i++)
            {
                Solution[i] = solver.GetValue(a[i]).ToDouble();
            }
            return Solution;
            //MessageBox.Show("xong roi");
            /*for (int l = 0; l < a.Length; l++)
            {
                MessageBox.Show(solver.GetValue(a[l]).ToDouble().ToString());
            }*/
        }
        public void Excel_into_DataGrid(RadGridView dataGridView1, string path)
        {

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Open(path);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;

            int rcount = worksheet.UsedRange.Rows.Count;
            int ccount=worksheet.UsedRange.Columns.Count;


            //Initializing Columns
            dataGridView1.ColumnCount = worksheet.UsedRange.Columns.Count;
            for (int j = 1; j < dataGridView1.ColumnCount; j++)
            {

                dataGridView1.Columns[j].HeaderText = worksheet.Cells[1, j].Value;
                dataGridView1.Columns[j].Name = j.ToString();
                dataGridView1.Columns[j].Width = 100;
            }
            //MessageBox.Show("aaa");
           
            for (int i = 0; i < rcount; i++)
            {
                GridViewDataRowInfo rowInfo = new GridViewDataRowInfo(dataGridView1.MasterView);
                for (int j = 1; j < ccount; j++)
                {
                    rowInfo.Cells[j].Value = worksheet.Cells[i + 2, j].Value;
                    //dataGridView1.Rows[i].Cells["Column1"].Value = worksheet.Cells[i + 1, 1].Value;
                    //dataGridView1.Rows[i].Cells["Column2"].Value = worksheet.Cells[i + 1, 2].Value;
                    //dataGridView1.Rows.Add(worksheet.Cells[i + 2, 1].Value, worksheet.Cells[i + 2, 2].Value);
                }
                dataGridView1.Rows.Add(rowInfo);
                //rowInfo.Dispose();
            }
            object misValue = System.Reflection.Missing.Value;
            workbook.Close(true, misValue, misValue);
            app.Quit();
        }

        public DataTable ImportExcel_DataGrid(string path,string Name)
        {
            DataTable data = new DataTable();
          
                //DataTable dt = new DataTable();

               // String name = "Items";
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                path + ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

                OleDbConnection con = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + Name + "$]", con);
                con.Open();

                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                
                sda.Fill(data);
                //datatagrid1.DataSource = data;
                sda.Dispose();
                con.Close();
                
          
               
            
            return data;
        }
        public void Formatgridview(object sender, RowFormattingEventArgs e,RadGridView gr)
        {

            if (gr.Rows.Count > 0)
            {
                e.RowElement.DrawFill = true;

                if (int.Parse(e.RowElement.RowInfo.Cells[0].Value.ToString()) % 2 == 0)
                {
                    
                    e.RowElement.BackColor = System.Drawing.Color.AliceBlue;

                }
                else
                {
                    
                    e.RowElement.BackColor = System.Drawing.Color.Pink;
                }

            }
            
            
        }
    }
}
